#coding=utf-8
#@Time:2020/5/11 11:41 AM
#@Author:希哥
#@File:producr_delay.py
import time
import datetime
import sql
import sys
import dingding
import requests

reload(sys)
sys.setdefaultencoding('utf8')

'''
    产品延期
'''
def delay(product_id, date, operator):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    if product_id == '':
        return '请填写正确的产品id'
    if date == '':
        return '请填写日期，格式：2020-07-01'
    else:
        try:
            new_date = date + ' 23:59:59.999000'
            #凭证延期sql
            biz_certificate_sql = "  UPDATE biz_certificate biz SET end_time = '"+ new_date +"' where biz.product_snapshot_id in (select b.id from product_snapshot b where b.product_id = '"+ product_id + "');"
            #凭证状态修改sql
            biz_certificate_status_sql = "  UPDATE biz_certificate biz SET `status` = 'normal' where `status` = 'expired' AND  biz.product_snapshot_id in (select b.id from product_snapshot b where b.product_id = '"+ product_id +"');"
            #凭证组延期sql
            biz_certificate_group_sql = "  UPDATE biz_certificate_group biz SET end_time = '"+ new_date +"' where biz.product_snapshot_id in (select b.id from product_snapshot b where b.product_id = '"+ product_id +"');"
            #凭证组状态修改sql
            biz_certificate_group_status_sql = "  UPDATE biz_certificate_group biz SET `status` = 'normal' where `status` = 'expired' AND  biz.product_snapshot_id in (select b.id from product_snapshot b where b.product_id = '"+ product_id +"');"
            #修改sku上面的时间显示sql
            product_sku_record_sql = "UPDATE product_sku_record SET end_time = '"+ new_date +"' WHERE end_time is NOT NULL AND product_snapshot_id in (select b.id from product_snapshot b where b.product_id = '"+ product_id +"');"
            '''
                依次执行上述sql
            '''
            sql.update(biz_certificate_sql, 'hupo_online')
            sql.update(biz_certificate_status_sql, 'hupo_online')
            sql.update(biz_certificate_group_sql, 'hupo_online')
            sql.update(biz_certificate_group_status_sql, 'hupo_online')
            sql.update(product_sku_record_sql, 'hupo_online')
            '''
                写入操作日志记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (now, product_id, '产品延期', operator, '产品ID：' + str(product_id) + '已延期至：' + str(new_date))
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '，产品id：' + product_id + '调整售卖日期至：' + str(new_date) + '成功，请前往sku配置页面保存更新一次，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return msg
        except:
            '''
                写入操作日志记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (now, product_id, '产品延期', operator, '操作失误请重新操作')
            sql.insert(write_sql, 'hupo_online')
            return '操作失误请重新操作'

'''
    修改结算价
'''
def change_cost_price(product_id, show_title, cost_price, start_time, end_time, operator):
    if product_id == '':
        return '请填写正确的产品id'
    if show_title == '':
        return '请填写sku名称'
    if cost_price == '':
        return '请填写新的结算价'
    else:
        now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
        new_cost_price = str(float(cost_price) * 100)
        select_cost_price_sql = "SELECT cost_price FROM product_sku_record WHERE product_id = '"+ product_id +"' AND alias_title = '"+ show_title +"'"
        origin_cost_price = sql.search(select_cost_price_sql, 'hupo_online')
        sku_cost_sql = "UPDATE product_sku_record SET cost_price = '"+ new_cost_price +"' WHERE product_id = '"+ product_id +"' AND alias_title = '"+ show_title +"'"
        sku_reservation_sql = "UPDATE product_sku_reservation SET cost_price = '"+ new_cost_price +"' WHERE sku_code in (SELECT sku_code FROM product_sku_record WHERE product_id = '" + product_id +"' AND alias_title = '"+ show_title +"') AND date >= '"+ start_time +"' AND date <= '"+ end_time +"' AND product_id = '" + product_id + "'"
        sku_reservation_total = "UPDATE product_sku_reservation SET cost_price = '"+ new_cost_price +"' WHERE sku_code in (SELECT sku_code FROM product_sku_record WHERE product_id = '"+ product_id +"' AND alias_title = '"+ show_title +"') AND product_id = '" + product_id + "'"
        if start_time == '' and end_time == '':
            sql.update(sku_cost_sql, 'hupo_online')
            sql.update(sku_reservation_total, 'hupo_online')
            '''
                写入操作日志记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
            now, product_id, '修改结算价', operator, 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) +'元，全部修改，修改后的结算价为：' + cost_price + '元')
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '，产品id：' + product_id + 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) +'元，全部修改，修改后的结算价为：' + cost_price + '元' + '成功，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) +'元，全部修改，修改后的结算价为：' + cost_price + '元'
        else:
            sql.update(sku_cost_sql, 'hupo_online')
            sql.update(sku_reservation_sql, 'hupo_online')
            '''
                写入操作日志记录
            '''
            write_sql = "insert into xige_hp_test.order_fix_record(create_time,order_no,operator_type,operator,result) values('%s','%s','%s','%s','%s')" % (
            now, product_id, '修改结算价', operator, 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) + '元，修改时间区间为'+ start_time + '---' + end_time +'，修改后的结算价为：' + cost_price + '元')
            sql.insert(write_sql, 'hupo_online')
            '''
                钉钉消息通知
            '''
            msg = '任务提示：' + str(now) + '，产品id：' + product_id + 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) + '元，修改时间区间为'+ start_time + '---' + end_time +'，修改后的结算价为：' + cost_price + '元' + '成功，操作人：' + str(operator)
            dingding.DingTalk_Base().send_msg(msg)
            return 'sku名称：' + show_title + '，原结算价为：' + str(int(origin_cost_price) / 100) + '元，修改时间区间为'+ start_time + '---' + end_time +'，修改后的结算价为：' + cost_price + '元'


'''
    添加商品自动上下架的记录到数据库中
'''
def add_sale_product(product_id, date, type, operator):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    '''
        检查该产品是否已配置过自动上下架，若已配置，则更新对应记录
    '''
    if product_id == '':
        return '请填写正确的产品id'
    if date == '':
        return '请填写正确的上下架日期'
    if type == 'None':
        return '请选择上下架类型'
    else:
        check_id_sql = "SELECT product_id FROM xige_hp_test.auto_sale_product WHERE product_id = '"+ product_id +"' AND dt = 0 AND do_type = '"+ type +"'"
        check_type_sql = "SELECT do_type FROM xige_hp_test.auto_sale_product WHERE product_id = '" + product_id + "' AND dt = 0 AND do_type = '"+ type +"'"
        check_date_sql = "SELECT do_date FROM xige_hp_test.auto_sale_product WHERE product_id = '" + product_id + "' AND dt = 0 AND do_type = '"+ type +"'"
        id = sql.search(check_id_sql, 'hupo_online')
        do_type = sql.search(check_type_sql, 'hupo_online')
        do_date = sql.search(check_date_sql, 'hupo_online')
        if product_id == id and type == do_type:
            update_sql = "UPDATE xige_hp_test.auto_sale_product SET do_date = '"+ date +"' WHERE product_id = '"+ product_id +"' AND do_type = '"+ type +"' AND dt = 0"
            sql.update(update_sql, 'hupo_online')
            if type == 'on':
                product_sql = "UPDATE product SET remark = '" + str(operator) + "设置自动上架：" + str(date) + "' WHERE id = '" + product_id + "';"
                sql.update(product_sql, 'hupo_online')
                return '商品：' + product_id + '已有配置记录：在'+ do_date +'上架，现更新为：' + date + '点自动上架！'
            if type == 'off':
                product_sql = "UPDATE product SET remark = '" + str(operator) + "设置自动下架：" + str(date) + "' WHERE id = '" + product_id + "';"
                sql.update(product_sql, 'hupo_online')
                return '商品：' + product_id + '已有配置记录：在'+ do_date +'下架，现更新为：' + date + '点自动下架！'
        else:
            write_sql = "insert into xige_hp_test.auto_sale_product(product_id,do_date,do_type,dt,create_time,update_time,result,operator) values('%s','%s','%s','%s','%s','%s','%s','%s')" % (
                product_id, date, type, '0', now, now, '', operator)
            sql.insert(write_sql, 'hupo_online')
            if type == 'on':
                product_sql = "UPDATE product SET remark = '" + str(operator) + "设置自动上架：" + str(date) + "' WHERE id = '" + product_id + "';"
                sql.update(product_sql, 'hupo_online')
                return '商品：' + product_id + '设置于：' + date + '自动上架成功！'
            if type == 'off':
                product_sql = "UPDATE product SET remark = '" + str(operator) + "设置自动下架：" + str(date) + "' WHERE id = '" + product_id + "';"
                sql.update(product_sql, 'hupo_online')
                return '商品：' + product_id + '设置于：' + date + '自动下架成功！'

'''
    自动上下架函数
'''
def auto_sale_product(product_id, type, date, operator):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    '''
        上下架商品的sql
    '''
    data = {'token': 'ed343bca1a1d4a39bd7646431fab9af0', 'id': str(product_id)}
    s = requests.session()
    if type == 'on':
        msg = '自动上架成功'
        product_sale = s.put('https://mall-admin.hupovip.cn/hp-mall-admin/v1/product/sale', data=data)
        product_sql = "UPDATE product SET remark = '"+ operator +"设置自动上架："+ date +"' WHERE id = '"+ product_id +"';"
        update_sql = "UPDATE xige_hp_test.auto_sale_product SET result = '"+ msg +"',dt = 1,update_time = '"+ now +"' WHERE product_id = '"+ product_id +"' AND do_type = '"+ type +"'"
        sql.update(product_sql, 'hupo_online')
        sql.update(update_sql, 'hupo_online')
        return product_sale.text
    if type == 'off':
        msg = '自动下架成功'
        product_unshelve = s.put('https://mall-admin.hupovip.cn/hp-mall-admin/v1/product/unshelve', data=data)
        product_sql = "UPDATE product SET remark = '"+ operator +"设置自动下架："+ date +"' WHERE id = '"+ product_id +"';"
        update_sql = "UPDATE xige_hp_test.auto_sale_product SET result = '"+ msg +"',dt = 1,update_time = '"+ now +"' WHERE product_id = '" + product_id + "' AND do_type = '"+ type +"'"
        sql.update(product_sql, 'hupo_online')
        sql.update(update_sql, 'hupo_online')
        return product_unshelve.text

'''
    添加产品库存报警记录
'''
def add_prodcut_stock(product_id, stock, operator):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    '''
        检查该产品是否已配置过自动上下架，若已配置，则更新对应记录
    '''
    if product_id == '':
        return '请填写正确的产品id'
    if stock == '':
        return '请填写正确的库存报警数量'
    else:
        check_id_sql = "SELECT product_id FROM xige_hp_test.product_check_stock WHERE product_id = '" + product_id + "'"
        check_stock_sql = "SELECT stock FROM xige_hp_test.product_check_stock WHERE product_id = '" + product_id + "'"
        check_producut_id = sql.search(check_id_sql , 'hupo_online')
        product_stock = sql.search(check_stock_sql , 'hupo_online')
        if product_id == check_producut_id:
            update_sql = "UPDATE xige_hp_test.product_check_stock SET stock = '"+ stock +"',operator = '"+ str(operator) +"',update_time = '"+ now +"' WHERE product_id = '"+ product_id +"'"
            sql.update(update_sql , 'hupo_online')
            msg = '产品id：' + product_id + '，原总库存报警数为：' + product_stock + '现已调整为：' + stock
            return msg
        else:
            write_sql = "insert into xige_hp_test.product_check_stock(product_id,stock,create_time,update_time,result,operator) values('%s','%s','%s','%s','%s','%s')" % (
                product_id, stock, now, now, '', operator)
            sql.insert(write_sql, 'hupo_online')
            msg = '产品id：' + product_id + '，设置总库存报警数为：' + stock
            return msg


'''
    检查商品的库存
'''
def check_product_stock(product_id, stock, operator):
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(time.time()))  # 数据产生日期
    check_sql = "SELECT p.title,SUM(pss.stock) FROM product_sku_stock as pss LEFT JOIN product as p ON pss.product_id = p.id WHERE p.id = '"+ product_id +"' GROUP BY p.id"
    result = sql.search_total(check_sql , 'hupo_online')
    product_title = result[0][0]
    product_stock = int(result[0][1])
    stock = int(stock)
    if product_stock <= stock:
        msg = '任务提示：' + str(now) + '，产品名：' + product_title + '，库存报警!当前总库存为：' + str(product_stock) + '，库存报警值为：' + str(stock) + ',操作人：' + str(operator)
        update_sql = "UPDATE xige_hp_test.product_check_stock SET result = '"+ msg +"',update_time = '"+ now +"' WHERE product_id = '"+ product_id + "'"
        sql.update(update_sql,'hupo_online')
        dingding.DingTalk_Base().send_msg(msg)
        print msg
        return msg
    else:
        msg = str(now) + '，产品名：' + product_title + '，当前库存为：' + str(product_stock) + ',库存无需报警,操作人：' + str(operator)
        update_sql = "UPDATE xige_hp_test.product_check_stock SET result = '" + msg + "',update_time = '"+ now +"' WHERE product_id = '" + product_id + "'"
        sql.update(update_sql, 'hupo_online')
        print msg
        return msg


'''
    判断日期是否为当天
'''
def is_today(target_date):
    """
    2020-03-25 17:03:55
    Detects if the date is current date
    :param target_date:
    :return: Boolean
    """
    # Get the year, month and day
    c_year = datetime.datetime.now().year
    c_month = datetime.datetime.now().month
    c_day = datetime.datetime.now().day
    c_hour = datetime.datetime.now().hour

    # Disassemble the date
    date_list = target_date.split(" ")[0].split("-")
    t_year = int(date_list[0])
    t_month = int(date_list[1])
    t_day = int(date_list[2])
    t_hour = int(date_list[3])

    final = False
    # Compare years, months and days
    if c_year == t_year and c_month == t_month and c_day == t_day and c_hour == t_hour:
        final = True

    return final


if __name__=='__main__':
    print add_sale_product('14870305367412881298','2020-09-30','on','希哥')
    # print is_today('2020-05-15-9')
    # print check_product_stock('14870305367437441810' , '200' , '希哥')
